﻿using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.AspNet.Identity;
using Microsoft.Owin.Security;
using SGIMail.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Security.Claims;

namespace SGIMail.DAL
{
    public class DbAccess : IDisposable
    {
        public ApplicationUserStore<ApplicationUser> UserStore { get; private set; }

        private IAuthenticationManager AuthenticationManager
        {
            get
            {
                return HttpContext.Current.GetOwinContext().Authentication;
            }
        }

        /// <summary>
        /// Constructor
        /// </summary>
        public DbAccess()
            : this(new ApplicationUserStore<ApplicationUser>(new ApplicationDbContext()))
        {

        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="userStore">DbContext</param>
        public DbAccess(ApplicationUserStore<ApplicationUser> userStore)
        {
            UserStore = userStore;
        }

        /// <summary>
        /// SaveChanges
        /// </summary>
        /// <returns>Result in int.</returns>
        public int SaveChanges()
        {
            return UserStore.DbContext.SaveChanges();
        }

        /// <summary>
        /// SaveChangesAsync
        /// </summary>
        /// <returns>Result in int.</returns>
        public async Task SaveChangesAsync()
        {
            await UserStore.DbContext.SaveChangesAsync();
        }

        #region CONTACT

        /// <summary>
        /// Saves the spreadsheet data to the db and updates or adds corresponding keys.
        /// </summary>
        /// <param name="contacts">Collection of Contacts from the spreadsheet.</param>
        /// <param name="ev">The event the data is related to.</param>
        /// <returns>void/Task</returns>
        public async Task SaveSpreadsheetData(ICollection<Contact> contacts, int eventId)
        {
            string Email = "";

            using (UserStore.DbContext = new ApplicationDbContext())
            {
                if (string.IsNullOrWhiteSpace(Email = await GetEmailFromCurrentIdentity()))
                    Email = GetIdentity();

                var user = await UserStore.FindByNameAsync(Email);

                foreach (var item in contacts)
                {
                    await AddContact(item);

                    var contactQuery = (from q in UserStore.DbContext.Contacts
                                        where q.Email == item.Email
                                        select q.ContactId).SingleOrDefault();

                    await RegisterEvent(contactQuery, eventId, Category.Registered);
                }
            }
        }

        /// <summary>
        /// Gets the email from the current identity.
        /// </summary>
        /// <returns>Email as a string.</returns>
        public async Task<string> GetEmailFromCurrentIdentity()
        {
            AuthenticateResult identity = default(AuthenticateResult);
            Claim emailClaim = default(Claim);

            if ((identity = await AuthenticationManager.AuthenticateAsync(DefaultAuthenticationTypes.ExternalCookie)) != null)
            {
                if ((emailClaim = identity.Identity.FindFirst(ClaimTypes.Email)) != null)
                {
                    return emailClaim == null ? null : emailClaim.Value;
                }
            }

            return null;
        }

        /// <summary>
        /// Gets the email from the current identity.
        /// </summary>
        /// <returns>Email as a string.</returns>
        public string GetIdentity()
        {
            return AuthenticationManager.User.Identity.Name;
        }

        /// <summary>
        /// Adds and links the contact to the user, or updates the contact.
        /// </summary>
        /// <param name="contact">The contact to be added or updated.</param>
        public async Task AddContact(Contact contact)
        {
            UserContacts u = new UserContacts();
            u.ApplicationUser = new List<ApplicationUser>();
            u.Contact = new List<Contact>();
            ApplicationUser user = default(ApplicationUser);
            string email = "";

            if (string.IsNullOrWhiteSpace(email = GetIdentity()))
                email = await GetEmailFromCurrentIdentity();

            user = await UserStore.FindByNameAsync(email);

            if (contact.Company != null)
            {
                if (!string.IsNullOrWhiteSpace(contact.Company.CompanyName))
                {
                    var companyQuery = (from q in UserStore.DbContext.Company
                                        where q.CompanyName == contact.Company.CompanyName
                                        select q).FirstOrDefault();

                    if (companyQuery != null)
                    {
                        contact.Company = companyQuery;
                    }
                }
            }

            var query = UserStore.DbContext.Contacts.SingleOrDefault(x => x.Email == contact.Email);

            if (query == null)
            {
                u.ApplicationUser.Add(user);
                u.Contact.Add(contact);
                user.UserContacts.Add(u);
                await UserStore.UpdateAsync(user);
            }

            else
            {
                var userQuery = (from q in UserStore.DbContext.UserContacts
                                 from c in q.Contact
                                 from w in q.ApplicationUser
                                 where c.ContactId == query.ContactId &&
                                     w.Id == user.Id
                                 select q).SingleOrDefault();

                if (userQuery == null)
                {
                    u.ApplicationUser.Add(user);
                    u.Contact.Add(query);
                    user.UserContacts.Add(u);
                    await UserStore.UpdateAsync(user);
                }

                else
                {
                    query.CompanyId = contact.Company.CompanyId == 0 ? query.Company.CompanyId : contact.Company.CompanyId;
                    query.Email = contact.Email == null ? query.Email : contact.Email;
                    query.EventDetails = contact.EventDetails == null ? query.EventDetails : contact.EventDetails;
                    query.Name = contact.Name == null ? query.Name : contact.Name;
                    query.Phone = contact.Phone == null ? query.Phone : contact.Phone;
                    query.UserContacts = contact.UserContacts == null ? query.UserContacts : contact.UserContacts;

                    UserStore.DbContext.Contacts.Attach(query);
                    UserStore.DbContext.Entry(query).State = EntityState.Modified;
                    UserStore.DbContext.Configuration.ValidateOnSaveEnabled = false;
                    await SaveChangesAsync();
                }
            }
        }

        /// <summary>
        /// Deletes the link from the contact to the user, however the contact data remains in the database.
        /// </summary>
        /// <param name="contact">The contact to be deleted from the user.</param>
        public async Task DeleteContact(Contact contact)
        {
            using (UserStore.DbContext = new ApplicationDbContext())
            {
                var user = await UserStore.FindByNameAsync(GetIdentity());

                var query = (from q in UserStore.DbContext.UserContacts
                             from u in q.ApplicationUser
                             from c in q.Contact
                             where u.Id == user.Id &&
                             c.ContactId == contact.ContactId
                             select q).SingleOrDefault();

                UserStore.DbContext.UserContacts.Remove(query);
                await SaveChangesAsync();
            }
        }

        /// <summary>
        /// Find a contact by id.
        /// </summary>
        /// <param name="id">The contact's id.</param>
        /// <returns>A contact.</returns>
        public Contact FindContact(int? id)
        {
            Contact contact = new Contact();

            if ((contact = UserStore.DbContext.Contacts.Find(id)) != null)
                return contact;

            else
                return null;
        }

        /// <summary>
        /// Gets a list of contacts which is linked to the user.
        /// </summary>
        /// <returns>List<Contact> which are linked to the user.</returns>
        public async Task<List<Contact>> GetUserContacts()
        {
            string email = GetIdentity();
            var user = await UserStore.FindByNameAsync(email);

            var query = (from q in UserStore.DbContext.Contacts
                         from q2 in q.UserContacts
                         from q3 in q2.ApplicationUser
                         where q3.Id == user.Id
                         select q).ToList();

            return query;
        }

        /// <summary>
        /// Gets a contact by id.
        /// </summary>
        /// <param name="id">The id of the contact.</param>
        /// <returns></returns>
        public Contact GetContact(int id)
        {
            var query = (from q in UserStore.DbContext.Contacts
                         where q.ContactId == id
                         select q).FirstOrDefault();

            return query;
        }

        /// <summary>
        /// Gets a list of ContactViewModel which are linked to the user.
        /// </summary>
        /// <returns>List<ContactViewModel> which are linked to the user.</returns>
        public async Task<List<ContactViewModel>> GetUserContactViewModel()
        {
            ContactViewModel cvm = new ContactViewModel();
            List<ContactViewModel> cvmList = new List<ContactViewModel>();
            var user = await UserStore.FindByNameAsync(GetIdentity());

            var query = (from q in UserStore.DbContext.UserContacts
                         from u in q.ApplicationUser
                         from c in q.Contact
                         where u.Id == user.Id
                         select c).ToList();

            foreach (var item in query)
            {
                cvm.Contact = item;
                cvmList.Add(cvm);
            }

            return cvmList;
        }

        /// <summary>
        /// Gets a specific ContactViewModel by a contact's id.
        /// </summary>
        /// <param name="id">The id of a contact.</param>
        /// <returns>ContactViewModel</returns>
        public async Task<ContactViewModel> GetContactViewDetails(int? id)
        {
            ContactViewModel cvm = new ContactViewModel();
            var user = await UserStore.FindByNameAsync(GetIdentity());

            var contactQuery = (from q in UserStore.DbContext.Contacts
                                where q.ContactId == id
                                select q).SingleOrDefault();

            var eventQuery = (from q in UserStore.DbContext.EventDetails
                              where q.Contacts.ContactId == contactQuery.ContactId
                              select q).ToList();

            cvm.Contact = contactQuery;
            cvm.EventDetails = eventQuery;

            return cvm;
        }

        /// <summary>
        /// Gets a contact's id by id.
        /// </summary>
        /// <param name="contactId">The contact's id.</param>
        /// <returns>The id of a contact.</returns>
        public int GetContactId(int contactId)
        {
            var query = (from q in UserStore.DbContext.Contacts
                         where q.ContactId == contactId
                         select q.ContactId).SingleOrDefault();

            return query;
        }

        /// <summary>
        /// Gets a contact's id by email.
        /// </summary>
        /// <param name="email">The email of the contact.</param>
        /// <returns>The id of a contact.</returns>
        public int GetContactId(string email)
        {
            var query = (from q in UserStore.DbContext.Contacts
                         where q.Email == email
                         select q.ContactId).FirstOrDefault();

            return query;
        }

        /// <summary>
        /// Checks if the contact exists in the database.
        /// </summary>
        /// <param name="contactId">The id of a contact.</param>
        /// <returns>boolean whether the contact exists or not.</returns>
        public bool ContactExists(int contactId)
        {
            return UserStore.DbContext.Contacts.Any(x => x.ContactId == contactId);
        }

        /// <summary>
        /// Returns the date when a contact last was active.
        /// </summary>
        /// <param name="contactId">The id of a contact.</param>
        /// <returns>DateTime when the contact last was active.</returns>
        public DateTime? LastDateActiveForContact(int contactId)
        {
            var query = (from q in UserStore.DbContext.EventDetails
                         where q.ContactId == contactId
                         select q).ToList();

            if (query != null && query.Any(x => x.Date != null))
                return query.Select(x => x.Date).Max();

            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="contactId"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int GetContactCountByCategory(int? contactId, Category category)
        {
            return (from q in UserStore.DbContext.EventDetails
                    where q.ContactId == contactId &&
                    q.Category == category
                    select q.ContactId).Count();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetContactParticipation(int? id)
        {
            string participation;
            if (id != null)
            {
                int utskick = GetContactCountByCategory(id, Category.Mailed);
                int reggade = GetContactCountByCategory(id, Category.Registered);
                participation = reggade.ToString() + "/" + utskick.ToString();
                return participation;
            }

            else
            {
                participation = "unknown";
                return participation;
            }
        }

        #endregion

        #region EVENT

        /// <summary>
        /// Registers or updates a event made when a contact has been mailed, or a contact has registered themselves etc.
        /// </summary>
        /// <param name="contactId">The id of the contact.</param>
        /// <param name="eventId">The id of the event.</param>
        /// <returns>void</returns>
        public async Task RegisterEvent(int contactId, int eventId, Category category)
        {
            EventDetails ed = new EventDetails();

            var query = (from q in UserStore.DbContext.EventDetails
                         where q.ContactId == contactId &&
                         q.EventId == eventId
                         select q).SingleOrDefault();

            if (query == null)
            {
                ed = new EventDetails()
                {
                    Category = category,
                    ContactId = contactId,
                    Date = DateTime.Now,
                    EventId = eventId,
                };

                UserStore.DbContext.EventDetails.Add(ed);
            }

            else
            {
                UserStore.DbContext.EventDetails.Attach(query);
                UserStore.DbContext.Entry(query).State = EntityState.Modified;
                UserStore.DbContext.Configuration.ValidateOnSaveEnabled = false;
            }

            await SaveChangesAsync();
        }

        /// <summary>
        /// Creates event even if similar event exists.
        /// </summary>
        /// <param name="e">The event data.</param>
        public void CreateEvent(Event e)
        {
            UserStore.DbContext.Events.Add(e);
            SaveChanges();
        }

        /// <summary>
        /// Queries if the event already exists, if it doesn't save Event to db.
        /// Else update the event.
        /// </summary>
        /// <param name="e">Event</param>
        /// <returns>void/Task</returns>
        public async Task CreateOrUpdateEvent(Event e)
        {
            var query = (from q in UserStore.DbContext.Events
                         where q.Name == e.Name
                         || q.EventId == e.EventId
                         select q).FirstOrDefault();

            if (query == null)
            {
                UserStore.DbContext.Events.Add(e);
            }

            else
            {
                query.Date = e.Date != query.Date ? query.Date : e.Date;
                query.ImagePath = e.ImagePath == null ? query.ImagePath : e.ImagePath;
                query.Name = e.Name == null ? query.Name : e.Name;
                query.RegistrationURL = e.RegistrationURL == null ? query.RegistrationURL : e.RegistrationURL;
                query.Theme = e.Theme == null ? query.Theme : e.Theme;

                UserStore.DbContext.Events.Attach(query);
                UserStore.DbContext.Entry(query).State = EntityState.Modified;
                UserStore.DbContext.Configuration.ValidateOnSaveEnabled = false;
            }

            await SaveChangesAsync();
        }

        /// <summary>
        /// Deletes event by id.
        /// </summary>
        /// <param name="id">The id for the event to be deleted.</param>
        public void DeleteEvent(int id)
        {
            UserStore.DbContext.Events.Remove(GetEventById(id));
            SaveChanges();
        }

        /// <summary>
        /// Gets all the events.
        /// </summary>
        /// <returns>Gets the events as a IQueryable<Event></returns>
        public IQueryable<Event> GetEvents()
        {
            return UserStore.DbContext.Events;
        }

        /// <summary>
        /// Gets all the events where the dates are greater than today.
        /// </summary>
        /// <returns>IQueryable<Event></returns>
        public IQueryable<Event> GetCurrentEvents()
        {
            return UserStore.DbContext.Events.Where(x => x.Date > DateTime.Now);
        }

        /// <summary>
        /// Gets the event's image file path.
        /// </summary>
        /// <param name="eventId">The event's id.</param>
        /// <returns>filepath as a string.</returns>
        public string GetEventImage(int eventId)
        {
            var query = (from q in UserStore.DbContext.Events
                         where q.EventId == eventId
                         select q.ImagePath).FirstOrDefault();

            return query;
        }

        /// <summary>
        /// Get the event's id.
        /// </summary>
        /// <param name="eventId">The event's id.</param>
        /// <returns>id of the event.</returns>
        public int GetEventId(int eventId)
        {
            var query = (from q in UserStore.DbContext.Events
                         where q.EventId == eventId
                         select q.EventId).FirstOrDefault();

            return query;
        }

        /// <summary>
        /// Gets a event by id.
        /// </summary>
        /// <param name="id">The event's id.</param>
        /// <returns>Event object of the id.</returns>
        public Event GetEventById(int? id)
        {
            return UserStore.DbContext.Events.Find(id);
        }

        /// <summary>
        /// Checks if the event exists.
        /// </summary>
        /// <param name="id">The id of the event.</param>
        /// <returns>bool whether the event exists or not.</returns>
        public bool DoEventExist(int id)
        {
            return UserStore.DbContext.Events.Any(x => x.EventId == id);
        }

        /// <summary>
        /// Gets the EventDetails of a specified event.
        /// </summary>
        /// <param name="id">The Event's id.</param>
        /// <returns>A List of EventDetails.</returns>
        public ICollection<EventDetails> GetEventDetails(int? id)
        {
            return UserStore.DbContext.EventDetails.Where(x => x.EventId == id).ToList();
        }

        // ??
        public ICollection<EventDetails> GetThemeEventDetails()
        {
            return UserStore.DbContext.EventDetails.ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int GetEventDetailsCountByCategory(int? id, Category category)
        {
            var query = (from q in UserStore.DbContext.EventDetails
                         where q.EventId == id &&
                         q.Category == category
                         select q.ContactId).Count();

            return query;
        }

        /// <summary>
        /// Gets the event's registration url.
        /// </summary>
        /// <param name="eventId">The id of the event.</param>
        /// <returns>Url as a string.</returns>
        public string GetEventRegistrationUrl(int eventId)
        {
            return UserStore.DbContext.Events.SingleOrDefault(x => x.EventId == eventId).RegistrationURL;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetEventParticipation(int? id)
        {
            string participation;
            if (id != null)
            {
                int utskick = GetEventDetailsCountByCategory(id, Category.Mailed);
                int reggade = GetEventDetailsCountByCategory(id, Category.Registered);
                participation = reggade.ToString() + "/" + utskick.ToString();
                return participation;
            }

            else
            {
                participation = "unknown";
                return participation;
            }
        }

        /// <summary>
        /// Returns a event with a list of event details where the details is sorted and prioritized to show
        /// the registrations only if they exist else it shows clicked or mailed in descending priority.
        /// </summary>
        /// <param name="id">The id of the event.</param>
        /// <returns>The selected event.</returns>
        public EventViewModel GetEvent(int? id)
        {
            List<ContactViewModel> contactList = new List<ContactViewModel>();
            ContactViewModel cvm = new ContactViewModel();
            Event e = GetEventById(id);
            var eventDetailsList = GetEventDetails(id);

            foreach (var ed in eventDetailsList)
            {
                if (!contactList.Any(x => x.Email == ed.Contacts.Email))
                {
                    cvm = new ContactViewModel();
                    cvm.ID = ed.Contacts.ContactId;
                    cvm.Name = ed.Contacts.Name;
                    cvm.Email = ed.Contacts.Email;
                    cvm.Company = ed.Contacts.Company == null ? null : ed.Contacts.Company.CompanyName;

                    switch (ed.Category)
                    {
                        case Category.Mailed:
                            cvm.LatestActivity = ed.Category.ToString();
                            break;

                        case Category.Clicked:
                            cvm.LatestActivity = ed.Category.ToString();
                            break;

                        case Category.Registered:
                            cvm.LatestActivity = ed.Category.ToString();
                            break;
                        default:
                            break;
                    }

                    contactList.Add(cvm);
                }
            }

            EventViewModel ev = new EventViewModel();
            ev.Name = e.Name;
            ev.Theme = e.Theme.ToString();
            ev.Date = e.Date;
            ev.participation = GetEventParticipation(id);
            ev.contactList = contactList.OrderByDescending(x => x.LatestActivity == Category.Registered.ToString())
                .ThenBy(x => x.LatestActivity == Category.Clicked.ToString())
                .ThenBy(x => x.LatestActivity == Category.Mailed.ToString())
                .ThenBy(x => x.Email).ToList();
            ev.ImagePath = e.ImagePath;
            ev.id = e.EventId;

            return ev;
        }

        /// <summary>
        /// Gets event statistics for a event.
        /// </summary>
        /// <param name="id">The id of the event.</param>
        /// <returns></returns>
        public IQueryable<EventStatsticsViewModel> GetEventStatistics(int id)
        {
            IQueryable<EventStatsticsViewModel> data = from q in UserStore.DbContext.EventDetails
                                                       where q.EventId == id
                                                       group q by q.Category into categoryGroup
                                                       select new EventStatsticsViewModel()
                                                       {
                                                           Activity = categoryGroup.Key.ToString(),
                                                           ContactCount = categoryGroup.Count()
                                                       };

            return data;
        }

        /// <summary>
        /// Gets event statistics for a contact.
        /// </summary>
        /// <param name="id">The id of a contact.</param>
        /// <returns></returns>
        public IQueryable<EventStatsticsViewModel> GetEventStatisticsByContactId(int id)
        {
            IQueryable<EventStatsticsViewModel> data = from q in UserStore.DbContext.EventDetails
                                                       where q.ContactId == id
                                                       group q by q.Category into categoryGroup
                                                       select new EventStatsticsViewModel()
                                                       {
                                                           Activity = categoryGroup.Key.ToString(),
                                                           ContactCount = categoryGroup.Count()
                                                       };

            return data;
        }

        /// <summary>
        /// Gets event statistics by a theme.
        /// </summary>
        /// <param name="id">The id of a contact.</param>
        /// <returns></returns>
        public IQueryable<EventStatsticsViewModel> GetEventStatisticsByTheme(int id)
        {
            IQueryable<EventStatsticsViewModel> data = from theme in UserStore.DbContext.EventDetails
                                                       where theme.ContactId == id && theme.Category == Category.Registered
                                                       group theme by theme.Event.Theme into categoryGroup
                                                       select new EventStatsticsViewModel()
                                                       {
                                                           Activity = categoryGroup.Key.ToString(),
                                                           ContactCount = categoryGroup.Count()
                                                       };

            return data;
        }

        #endregion

        /// <summary>
        /// Disposes the context.
        /// </summary>
        public void Dispose()
        {
            if (UserStore.DbContext != null)
            {
                UserStore.DbContext.Dispose();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="disposing"></param>
        public void Dispose(bool disposing)
        {
            if (disposing &&
                UserStore.DbContext != null)
            {
                UserStore.DbContext.Dispose();
            }
        }
    }
}
